SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>Alias (SQL)</title>

</head>

<span id="openzim-page-title" class="mw-page-title-main"><span class="mw-page-title-main">Alias (SQL)</span></span>

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .ambox{border:1px solid #a2a9b1;border-left:10px solid #36c;background-color:#fbfbfb;box-sizing:border-box}.mw-parser-output .ambox+link+.ambox,.mw-parser-output .ambox+link+style+.ambox,.mw-parser-output .ambox+link+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+style+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+link+.ambox{margin-top:-1px}html body.mediawiki .mw-parser-output .ambox.mbox-small-left{margin:4px 1em 4px 0;overflow:hidden;width:238px;border-collapse:collapse;font-size:88%;line-height:1.25em}.mw-parser-output .ambox-speedy{border-left:10px solid #b32424;background-color:#fee7e6}.mw-parser-output .ambox-delete{border-left:10px solid #b32424}.mw-parser-output .ambox-content{border-left:10px solid #f28500}.mw-parser-output .ambox-style{border-left:10px solid #fc3}.mw-parser-output .ambox-move{border-left:10px solid #9932cc}.mw-parser-output .ambox-protection{border-left:10px solid #a2a9b1}.mw-parser-output .ambox .mbox-text{border:none;padding:0.25em 0.5em;width:100%}.mw-parser-output .ambox .mbox-image{border:none;padding:2px 0 2px 0.5em;text-align:center}.mw-parser-output .ambox .mbox-imageright{border:none;padding:2px 0.5em 2px 0;text-align:center}.mw-parser-output .ambox .mbox-empty-cell{border:none;padding:0;width:1px}.mw-parser-output .ambox .mbox-image-div{width:52px}@media(min-width:720px){.mw-parser-output .ambox{margin:0 10%}}@media print{body.ns-0 .mw-parser-output .ambox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style>

<p>An <b>alias</b> is a feature of <a href="SQL" title="SQL">SQL</a> that is supported by most, if not all, <a href="RDBMS" class="mw-redirect" title="RDBMS">relational database management systems (RDBMSs)</a>. Aliases provide users with the ability to reduce the amount of code required for a query, and to make queries simpler to understand. In addition, aliasing is required when doing self joins (i.e. joining a table with itself.)

</p><p>In SQL, you can alias tables and <a href="Column_(database)" title="Column (database)">columns</a>. A table alias is called a <b>correlation name</b>, according to the SQL standard.<sup id="cite_ref-1" class="reference"><a href="#cite_note-1"><span class="cite-bracket">[</span>1<span class="cite-bracket">]</span></a></sup> A programmer can use an alias to temporarily assign another name to a table or column for the duration of the current <a href="Select_(SQL)" title="Select (SQL)">SELECT query</a>. Assigning an alias does not actually rename the column or table. This is often useful when either tables or their columns have very long or complex names. An alias name could be anything, but usually it is kept short. For example, it might be common to use a table alias such as "pi" for a table named "price_information".

</p><p>The general syntax of an alias is <code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">table_name</span><span class="w"> </span><span class="p">[</span><span class="k">AS</span><span class="p">]</span><span class="w"> </span><span class="n">alias_name</span></code>. Note that the AS keyword is completely optional and is usually kept for readability purposes. Here is some sample data that the queries below will be referencing:

</p>

<caption>Department Table

</caption>

<th>DepartmentID</th>

<th>DepartmentName

</th></tr>

<tr>

<td>Sales

</td></tr>

<tr>

<td>Engineering

</td></tr>

<tr>

<td>Clerical

</td></tr>

<tr>

<td>Marketing

</td></tr></tbody></table>

<p>Using a table alias:

</p>

</pre></div>

<p>We can also write the same query like this (Note that the AS clause is omitted this time):

</p>

</pre></div>

<p>A column alias is similar:

</p>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="n">d</span><span class="p">.</span><span class="n">DepartmentId</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">Id</span><span class="p">,</span><span class="w"> </span><span class="n">d</span><span class="p">.</span><span class="n">DepartmentName</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">Name</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">Department</span><span class="w"> </span><span class="n">d</span>

</pre></div>

<p>In the returned <a href="Result_set" title="Result set">result sets</a>, the data shown above would be returned, with the only exception being "DepartmentID" would show up as "Id", and "DepartmentName" would show up as "Name".

</p><p>Also, if only one table is being selected and the query is not using <a href="Join_(SQL)" title="Join (SQL)">table joins</a>, it is permissible to omit the table name or table alias from the column name in the SELECT statement. Example as follows:

</p>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="n">DepartmentId</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">Id</span><span class="p">,</span><span class="w"> </span><span class="n">DepartmentName</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">Name</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">Department</span><span class="w"> </span><span class="n">d</span>

</pre></div>

<p>Some systems, such as Postgres<sup id="cite_ref-2" class="reference"><a href="#cite_note-2"><span class="cite-bracket">[</span>2<span class="cite-bracket">]</span></a></sup> and Presto,<sup id="cite_ref-3" class="reference"><a href="#cite_note-3"><span class="cite-bracket">[</span>3<span class="cite-bracket">]</span></a></sup> support specifying column aliases together with table aliases. E.g.

</p>

</pre></div>

<p>would produce the same result set as before. In this syntax it is permissible to omit aliases for some column names. In the example, an alias was provided for DepartmentId, but omitted for DepartmentName. Columns with unspecified aliases will be left unaliased. This syntax is often used with expressions that do not produce useful table and column names, such as VALUES<sup id="cite_ref-4" class="reference"><a href="#cite_note-4"><span class="cite-bracket">[</span>4<span class="cite-bracket">]</span></a></sup> and UNNEST.<sup id="cite_ref-5" class="reference"><a href="#cite_note-5"><span class="cite-bracket">[</span>5<span class="cite-bracket">]</span></a></sup> As an example, one may conveniently test the above SQL statements without creating an actual Departments table by using expressions such as

</p>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">WITH</span><span class="w"> </span><span class="n">Department</span><span class="p">(</span><span class="n">DepartmentId</span><span class="p">,</span><span class="w"> </span><span class="n">DepartmentName</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="p">(</span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'HR'</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">'IT'</span><span class="p">))</span>

<span class="k">SELECT</span><span class="w"> </span><span class="n">DepartmentId</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">Id</span><span class="p">,</span><span class="w"> </span><span class="n">DepartmentName</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">Name</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">Department</span><span class="w"> </span><span class="n">d</span><span class="p">;</span>

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

<li id="cite_note-1"><span class="mw-cite-backlink"><b><a href="#cite_ref-1">^</a></b></span> <span class="reference-text">ANSI Standard SQL – Foundation Document – Date: 2010-10-14</span>

</li>

<li id="cite_note-2"><span class="mw-cite-backlink"><b><a href="#cite_ref-2">^</a></b></span> <span class="reference-text"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/13/queries-table-expressions.html#QUERIES-FROM">PostgreSQL: Documentation: 13: 7.2. Table Expressions</a></span>

</li>

<li id="cite_note-3"><span class="mw-cite-backlink"><b><a href="#cite_ref-3">^</a></b></span> <span class="reference-text"><a rel="nofollow" class="external free" href="https://prestodb.io/docs/0.248/sql/select.html">https://prestodb.io/docs/0.248/sql/select.html</a> SELECT — Presto 0.248 Documentation</span>

</li>

<li id="cite_note-4"><span class="mw-cite-backlink"><b><a href="#cite_ref-4">^</a></b></span> <span class="reference-text"><a rel="nofollow" class="external free" href="https://prestodb.io/docs/0.248/sql/values.html#examples">https://prestodb.io/docs/0.248/sql/values.html#examples</a> VALUES — Presto 0.248 Documentation</span>

</li>

<li id="cite_note-5"><span class="mw-cite-backlink"><b><a href="#cite_ref-5">^</a></b></span> <span class="reference-text"><a rel="nofollow" class="external free" href="https://prestodb.io/docs/0.248/sql/select.html#unnest">https://prestodb.io/docs/0.248/sql/select.html#unnest</a> SELECT — Presto 0.248 Documentation</span>

</li>

</ol></div></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .hlist dl,.mw-parser-output .hlist ol,.mw-parser-output .hlist ul{margin:0;padding:0}.mw-parser-output .hlist dd,.mw-parser-output .hlist dt,.mw-parser-output .hlist li{margin:0;display:inline}.mw-parser-output .hlist.inline,.mw-parser-output .hlist.inline dl,.mw-parser-output .hlist.inline ol,.mw-parser-output .hlist.inline ul,.mw-parser-output .hlist dl dl,.mw-parser-output .hlist dl ol,.mw-parser-output .hlist dl ul,.mw-parser-output .hlist ol dl,.mw-parser-output .hlist ol ol,.mw-parser-output .hlist ol ul,.mw-parser-output .hlist ul dl,.mw-parser-output .hlist ul ol,.mw-parser-output .hlist ul ul{display:inline}.mw-parser-output .hlist .mw-empty-li{display:none}.mw-parser-output .hlist dt::after{content:": "}.mw-parser-output .hlist dd::after,.mw-parser-output .hlist li::after{content:" · ";font-weight:bold}.mw-parser-output .hlist dd:last-child::after,.mw-parser-output .hlist dt:last-child::after,.mw-parser-output .hlist li:last-child::after{content:none}.mw-parser-output .hlist dd dd:first-child::before,.mw-parser-output .hlist dd dt:first-child::before,.mw-parser-output .hlist dd li:first-child::before,.mw-parser-output .hlist dt dd:first-child::before,.mw-parser-output .hlist dt dt:first-child::before,.mw-parser-output .hlist dt li:first-child::before,.mw-parser-output .hlist li dd:first-child::before,.mw-parser-output .hlist li dt:first-child::before,.mw-parser-output .hlist li li:first-child::before{content:" (";font-weight:normal}.mw-parser-output .hlist dd dd:last-child::after,.mw-parser-output .hlist dd dt:last-child::after,.mw-parser-output .hlist dd li:last-child::after,.mw-parser-output .hlist dt dd:last-child::after,.mw-parser-output .hlist dt dt:last-child::after,.mw-parser-output .hlist dt li:last-child::after,.mw-parser-output .hlist li dd:last-child::after,.mw-parser-output .hlist li dt:last-child::after,.mw-parser-output .hlist li li:last-child::after{content:")";font-weight:normal}.mw-parser-output .hlist ol{counter-reset:listitem}.mw-parser-output .hlist ol>li{counter-increment:listitem}.mw-parser-output .hlist ol>li::before{content:" "counter(listitem)"\a0 "}.mw-parser-output .hlist dd ol>li:first-child::before,.mw-parser-output .hlist dt ol>li:first-child::before,.mw-parser-output .hlist li ol>li:first-child::before{content:" ("counter(listitem)"\a0 "}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1236075235">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbox{box-sizing:border-box;border:1px solid #a2a9b1;width:100%;clear:both;font-size:88%;text-align:center;padding:1px;margin:1em auto 0}.mw-parser-output .navbox .navbox{margin-top:0}.mw-parser-output .navbox+.navbox,.mw-parser-output .navbox+.navbox-styles+.navbox{margin-top:-1px}.mw-parser-output .navbox-inner,.mw-parser-output .navbox-subgroup{width:100%}.mw-parser-output .navbox-group,.mw-parser-output .navbox-title,.mw-parser-output .navbox-abovebelow{padding:0.25em 1em;line-height:1.5em;text-align:center}.mw-parser-output .navbox-group{white-space:nowrap;text-align:right}.mw-parser-output .navbox,.mw-parser-output .navbox-subgroup{background-color:#fdfdfd}.mw-parser-output .navbox-list{line-height:1.5em;border-color:#fdfdfd}.mw-parser-output .navbox-list-with-group{text-align:left;border-left-width:2px;border-left-style:solid}.mw-parser-output tr+tr>.navbox-abovebelow,.mw-parser-output tr+tr>.navbox-group,.mw-parser-output tr+tr>.navbox-image,.mw-parser-output tr+tr>.navbox-list{border-top:2px solid #fdfdfd}.mw-parser-output .navbox-title{background-color:#ccf}.mw-parser-output .navbox-abovebelow,.mw-parser-output .navbox-group,.mw-parser-output .navbox-subgroup .navbox-title{background-color:#ddf}.mw-parser-output .navbox-subgroup .navbox-group,.mw-parser-output .navbox-subgroup .navbox-abovebelow{background-color:#e6e6ff}.mw-parser-output .navbox-even{background-color:#f7f7f7}.mw-parser-output .navbox-odd{background-color:transparent}.mw-parser-output .navbox .hlist td dl,.mw-parser-output .navbox .hlist td ol,.mw-parser-output .navbox .hlist td ul,.mw-parser-output .navbox td.hlist dl,.mw-parser-output .navbox td.hlist ol,.mw-parser-output .navbox td.hlist ul{padding:0.125em 0}.mw-parser-output .navbox .navbar{display:block;font-size:100%}.mw-parser-output .navbox-title .navbar{float:left;text-align:left;margin-right:0.5em}body.skin--responsive .mw-parser-output .navbox-image img{max-width:none!important}@media print{body.ns-0 .mw-parser-output .navbox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r920966791">

/* start https://en.wikipedia.org/ */

.mw-parser-output span.smallcaps{font-variant:small-caps}.mw-parser-output span.smallcaps-smaller{font-size:85%}

/* end https://en.wikipedia.org/ */

</style></div><div role="navigation" class="navbox" aria-labelledby="SQL70" style="padding:3px"><table class="nowraplinks mw-collapsible autocollapse navbox-inner" style="border-spacing:0;background:transparent;color:inherit"><tbody><tr><th scope="col" class="navbox-title" colspan="2"><style data-mw-deduplicate="TemplateStyles:r1239400231">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbar{display:inline;font-size:88%;font-weight:normal}.mw-parser-output .navbar-collapse{float:left;text-align:left}.mw-parser-output .navbar-boxtext{word-spacing:0}.mw-parser-output .navbar ul{display:inline-block;white-space:nowrap;line-height:inherit}.mw-parser-output .navbar-brackets::before{margin-right:-0.125em;content:"[ "}.mw-parser-output .navbar-brackets::after{margin-left:-0.125em;content:" ]"}.mw-parser-output .navbar li{word-spacing:-0.125em}.mw-parser-output .navbar a>span,.mw-parser-output .navbar a>abbr{text-decoration:inherit}.mw-parser-output .navbar-mini abbr{font-variant:small-caps;border-bottom:none;text-decoration:none;cursor:inherit}.mw-parser-output .navbar-ct-full{font-size:114%;margin:0 7em}.mw-parser-output .navbar-ct-mini{font-size:114%;margin:0 4em}html.skin-theme-clientpref-night .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}@media(prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}}@media print{.mw-parser-output .navbar{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><div id="SQL70" style="font-size:114%;margin:0 4em"><a href="SQL" title="SQL">SQL</a></div></th></tr><tr><th scope="row" class="navbox-group" style="width:1%">Versions</th><td class="navbox-list-with-group navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="SEQUEL" class="mw-redirect" title="SEQUEL">SEQUEL</a></li>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%"><a href="SQL_reserved_words" class="mw-redirect" title="SQL reserved words">Keywords</a></th><td class="navbox-list-with-group navbox-list navbox-even hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<li><span class="smallcaps"><a href="Create_(SQL)" class="mw-redirect" title="Create (SQL)">Create</a></span></li>

<li><span class="smallcaps"><a href="Delete_(SQL)" title="Delete (SQL)">Delete</a></span></li>

<li><span class="smallcaps"><a href="Group_by_(SQL)" title="Group by (SQL)">Group by</a></span></li>

<li><span class="smallcaps"><a href="Having_(SQL)" title="Having (SQL)">Having</a></span></li>

<li><span class="smallcaps"><a href="Insert_(SQL)" title="Insert (SQL)">Insert</a></span></li>

<li><span class="smallcaps"><a href="Merge_(SQL)" title="Merge (SQL)">Merge</a></span></li>

<li><span class="smallcaps"><a href="Order_by" title="Order by">Order by</a></span></li>

<li><span class="smallcaps"><a href="Prepare_(SQL)" class="mw-redirect" title="Prepare (SQL)">Prepare</a></span></li>

<li><span class="smallcaps"><a href="Select_(SQL)" title="Select (SQL)">Select</a></span></li>

<li><span class="smallcaps"><a href="Truncate_(SQL)" title="Truncate (SQL)">Truncate</a></span></li>

<li><span class="smallcaps"><a href="Set_operations_(SQL)" title="Set operations (SQL)">Union</a></span></li>

<li><span class="smallcaps"><a href="Update_(SQL)" title="Update (SQL)">Update</a></span></li>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Related</th><td class="navbox-list-with-group navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="Edgar_F._Codd" title="Edgar F. Codd">Edgar Codd</a></li>

<li><a href="Relational_database" title="Relational database">Relational database</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">ISO/IEC SQL parts</th><td class="navbox-list-with-group navbox-list navbox-even hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li>Framework</li>

<li>Foundation</li>

<li><a href="SQL/CLI" class="mw-redirect" title="SQL/CLI">Call-Level Interface</a></li>

<li><a href="SQL/PSM" title="SQL/PSM">Persistent Stored Modules</a></li>

<li><a href="SQL/MED" title="SQL/MED">Management of External Data</a></li>

<li><a href="SQL/OLB" title="SQL/OLB">Object Language Bindings</a></li>

<li><a href="SQL/Schemata" title="SQL/Schemata">Information and Definition Schemas</a></li>

<li><a href="SQL/JRT" title="SQL/JRT">SQL Routines and Types for the Java Programming Language</a></li>

<li><a href="SQL/XML" title="SQL/XML">XML-Related Specifications</a></li></ul>

</div></td></tr></tbody></table></div></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2023-10-02" href="https://en.wikipedia.org/wiki/?title=Alias_(SQL)&oldid=1178240347">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>